package com.project.api.sale.service;

import com.project.api.sale.dao.AdminDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class AdminService {
    @Autowired
    @Qualifier("mainJdbcTemplate")
    private JdbcTemplate mainJdbcTemplate;

    /**
     * 根据用户名查询密码信息
     * @param username
     * @return
     */
    public String queryPassword(String username){
        AdminDao adminDao = null;
        String sql = "select * from admindata where username=?";

        try{
            adminDao = (AdminDao) mainJdbcTemplate.queryForObject(sql, new Object[] {username}, new BeanPropertyRowMapper(AdminDao.class));
        } catch (EmptyResultDataAccessException e){
            adminDao = null;
        }

        if (adminDao == null){
            return "--null--";
        } else {
            return adminDao.getPassword();
        }
    }

    /**
     * 查询总的汇总数据信息
     * @return
     */
    public List queryTotalData(){
        List list = null;

        String  sql = "select sum(da.usertotal) usertotal, sum(da.ordertotal) ordertotal, sum(da.saletotal) saletotal, sum(da.ordersum) ordersum\n" +
                "from (\n" +
                "\t\tselect count(*) usertotal, 0 ordertotal, 0 saletotal, 0 ordersum\n" +
                "\t\tfrom `user` \n" +
                "\t\tunion ALL\n" +
                "\t\tselect 0 usertotal, count(*) ordertotal, 0 saletotal, 0 ordersum\n" +
                "\t\tfrom `order`\n" +
                "\t\tunion ALL\n" +
                "\t\tselect 0 usertotal, 0 ordertotal, sum(qty) saletotal, sum(price) ordersum\n" +
                "\t\tfrom order_detail\n" +
                ")da";
        try{
            list = mainJdbcTemplate.queryForList(sql);
        }catch (EmptyResultDataAccessException e){
            list = null;
        }

        return list;
    }

    /**
     * 查询用户信息
     * @return
     */
    public List queryUserData(){
        List list = null;
        String sql = "select a.openid, a.`name`, a.phone, a.address\n" +
                "from `user` a";

        try{
            list = mainJdbcTemplate.queryForList(sql);
        }catch (EmptyResultDataAccessException e){
            list = null;
        }
        return list;
    }

    /**
     * 根据openid查询个人的信息
     * @param openid
     * @return
     */
    public Map queryPersonData(String openid){
        Map map = null;
        String sql = "select a.openid, a.`name`, a.phone, a.address\n" +
                "from `user` a\n" +
                "where a.openid =?";
        try{
            map = mainJdbcTemplate.queryForMap(sql, new Object[] {openid});
        }catch (EmptyResultDataAccessException e){
            map = null;
        }
        return map;
    }

    /**
     * 更新用户信息
     * @param openid
     * @param username
     * @param phone
     * @param address
     */
    public void updatePersonData(String openid, String username, String phone, String address){
        String sql = "UPDATE `user` SET `name` =?, phone =?, address =? where openid =?";
        mainJdbcTemplate.update(sql, username, phone, address, openid);

    }

    /**
     * 删除个人信息
     * @param openid
     */
    public void deletePersonData(String openid){
        String sql = "DELETE FROM `user` where openid=?";
        mainJdbcTemplate.update(sql, openid);
    }

    /**
     * 查询全部的订单信息
     * @return
     */
    public Map queryOrderList(String filter){
        String sql = "SELECT a.id, a.number, a.`name` username, a.phone, a.province, a.city, a.dist, a.total, a.`status`, a.billStatus, a.createTime, b.`name` tcname, b.attribute, b.qty, a.description\n" +
                "from `order` a\n" +
                "left join order_detail b on a.id = b.orderId\n" +
                "order by a.createTime asc";
        Map map1 = null;
        Map map2 = null;
        Map map = new HashMap();

        List list = mainJdbcTemplate.queryForList(sql);

        for (int i=0; i<list.size(); i++){
            map1 = (Map)list.get(i);
            if (!map.containsKey((map1.get("id").toString()))){
                for (int j=0; j<list.size(); j++){
                    map2 = (Map)list.get(j);
                    String id1 = map1.get("id").toString();
                    String id2 = map2.get("id").toString();
                    String status = map2.get("status").toString();
                    if (filter.equals("all")){
                        Map tmp = new HashMap();
                        if (id1.equals(id2)){
                            if (map.containsKey(id2)){
                                //如果当前的集合中存在该订单时，只修改数据记录中的订单明细数据
                                tmp = (Map) map.get(id2);
                                String orderDtail = "";
                                if ((map2.get("tcname") != null || !map2.get("tcname").equals("")) && (map2.get("attribute") != null || !map2.get("attribute").equals(""))
                                        && (map2.get("qty") != null || map2.get("qty").equals(""))){
                                    orderDtail = tmp.get("orderDetail").toString() + map2.get("tcname") + " " + map2.get("attribute") + " 【" + map2.get("qty") + "份】 ";
                                }
                                tmp.put("orderDetail", orderDtail);
                                map.put(id2, tmp);
                            } else {
                                //如果当前的集合中没有存在该订单的数据时，初始化订单的数据

                                tmp.put("id", id2);
                                tmp.put("number", map2.get("number"));
                                tmp.put("username", map2.get("username"));
                                tmp.put("phone", map2.get("phone"));
                                tmp.put("total", map2.get("total"));
                                tmp.put("status", map2.get("status"));
                                tmp.put("billStatus", map2.get("billStatus"));
                                String date = map2.get("createTime").toString();
                                Long ts = Long.valueOf(date).longValue();
                                Timestamp timestamp = new Timestamp(ts) ;
                                DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                tmp.put("createTime", sdf.format(timestamp));
//                        tmp.put("tcname", map2.get("tcname"));
//                        tmp.put("attribute", map2.get("attribute"));
                                tmp.put("description", map2.get("description"));

                                String address = "";
                                String orderDetail = "";
                                if (map2.get("province") != null && map2.get("city") != null && map2.get("dist") != null){
                                    address = map2.get("province") + " " + map2.get("city") + " " + map2.get("dist") + " ";
                                }
                                if (map2.get("tcname") != null && map2.get("attribute") != null && map2.get("qty") != null){
                                    orderDetail = map2.get("tcname") + " " + map2.get("attribute") +  " 【" + map2.get("qty") + "份】 ";
                                }


                                tmp.put("address", address);
                                tmp.put("orderDetail", orderDetail);
                                map.put(id2, tmp);
                            }

                        }
                    } else {
                        if (status.equals(filter)){
                            Map tmp = new HashMap();
                            if (id1.equals(id2)){
                                if (map.containsKey(id2)){
                                    //如果当前的集合中存在该订单时，只修改数据记录中的订单明细数据
                                    tmp = (Map) map.get(id2);
                                    String orderDtail = "";
                                    if ((map2.get("tcname") != null || !map2.get("tcname").equals("")) && (map2.get("attribute") != null || !map2.get("attribute").equals(""))
                                            && (map2.get("qty") != null || map2.get("qty").equals(""))){
                                        orderDtail = tmp.get("orderDetail").toString() + map2.get("tcname") + " " + map2.get("attribute") + " 【" + map2.get("qty") + "份】 ";
                                    }
                                    tmp.put("orderDetail", orderDtail);
                                    map.put(id2, tmp);
                                } else {
                                    //如果当前的集合中没有存在该订单的数据时，初始化订单的数据

                                    tmp.put("id", id2);
                                    tmp.put("number", map2.get("number"));
                                    tmp.put("username", map2.get("username"));
                                    tmp.put("phone", map2.get("phone"));
                                    tmp.put("total", map2.get("total"));
                                    tmp.put("status", map2.get("status"));
                                    tmp.put("billStatus", map2.get("billStatus"));
                                    String date = map2.get("createTime").toString();
                                    Long ts = Long.valueOf(date).longValue();
                                    Timestamp timestamp = new Timestamp(ts) ;
                                    DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    tmp.put("createTime", sdf.format(timestamp));
                                    tmp.put("description", map2.get("description"));

                                    String address = "";
                                    String orderDetail = "";
                                    if (map2.get("province") != null && map2.get("city") != null && map2.get("dist") != null){
                                        address = map2.get("province") + " " + map2.get("city") + " " + map2.get("dist") + " ";
                                    }
                                    if (map2.get("tcname") != null && map2.get("attribute") != null && map2.get("qty") != null){
                                        orderDetail = map2.get("tcname") + " " + map2.get("attribute") +  " 【" + map2.get("qty") + "份】 ";
                                    }


                                    tmp.put("address", address);
                                    tmp.put("orderDetail", orderDetail);
                                    map.put(id2, tmp);
                                }

                            }
                        }

                    }

                }
            }
        }
        return map;
    }

    /**
     * 通过订单id查询订单列表
     * @param id
     * @return
     */
    public List querOrderListById(String id){
        String sql = "SELECT a.id, a.number, a.`name` username, a.phone, a.province, a.city, a.dist, a.createTime, a.total, a.`status`, b.id order_detailid, b.`name` tcname, b.attribute, b.qty, a.description\n" +
                "from `order` a\n" +
                "left join order_detail b on a.id = b.orderId\n" +
                "where a.id =?\n" +
                "order by a.createTime";
        List list = mainJdbcTemplate.queryForList(sql, id);
        return list;
    }

    public List queryOrderDataById(String id){
        String sql = "SELECT a.id, u.openid, a.number, a.`name` username, a.createTime, a.phone, a.total, a.`status`, b.id order_detailid, b.`name` tcname, b.attribute, b.qty, a.description\n" +
                "from `order` a\n" +
                "left join order_detail b on a.id = b.orderId\n" +
                "left join `user` u on a.userId = u.id\n" +
                "where a.id =?\n" +
                "order by a.createTime";
        List list = mainJdbcTemplate.queryForList(sql, id);
        return list;
    }

    /**
     * 删除订单信息
     * @param id
     */
    public void refundOrder(String id){
        String sql1 = "UPDATE `order` SET status =4 where id =?";
        mainJdbcTemplate.update(sql1, id);
    }

    /**
     * 更改订单状态
     * @param id
     */
    public void auditOrder(String id, String status){
        String sql = "UPDATE `order` SET `billStatus` =? where id =?";
        mainJdbcTemplate.update(sql, status, id);
    }

    /**
     * 修改订单状态信息
     * @param id
     * @param status
     */
    public void changeOrder(String id, String status){
        String sql = "UPDATE `order` SET `status` =? where id =?";
        mainJdbcTemplate.update(sql, status, id);
    }

    public List getCustomerService(){
        String sql = "select * from customerservice";
        List list = mainJdbcTemplate.queryForList(sql);
        return list;
    }

}
